
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop;
using System.Diagnostics;
using System.Reflection;
using Excel=Microsoft.Office.Interop.Excel;
using System.IO;
using System.Configuration;
using System.Text.RegularExpressions;


namespace TemplateGenerator
{
    public abstract class ExcelFileWriter<T>
    {
        private Excel.Application _excelApplication = null;
        private Excel.Workbooks _workBooks = null;
        private Excel._Workbook _workBook = null;
        private object _value = Missing.Value;
        private Excel.Sheets _excelSheets = null;
        private Excel._Worksheet _excelSheet = null;
        private Excel.Range _excelRange = null;
        private Excel.Font _excelFont = null;
        protected List<T> _list = null;
        /// <summary>
        /// User have to set the names of header in the derived class
        /// </summary>
        public abstract object[] Headers { get;}
        /// <summary>
        /// user have to parse the data from the list and pass each data along with the
        /// column and row name to the base fun, FillExcelWithData().
        /// </summary>
        /// <param name="list"></param>
        public abstract void FillRowData(List<T> list);

        /// <summary>
        /// get the data of object which will be saved to the excel sheet
        /// </summary>
        public abstract object[,] ExcelData { get;}
        /// <summary>
        /// get the no of columns
        /// </summary>
        public abstract int ColumnCount { get;}
        /// <summary>
        /// get the now of rows to fill
        /// </summary>
        public abstract int RowCount { get;}

        /// <summary>
        /// user can override this to make the headers not be in bold.
        /// by default it is true
        /// </summary>
        protected virtual bool BoldHeaders
        {
            get
            {
                return true;
            }
        }
        public void GenerateTemplate(string fileName, string outputLocation)
        {
            try
            {
                this.ActivateExcel(fileName);
                //string excelPath = ConfigurationManager.AppSettings["Path"];
                //string folderPath=excelPath.Substring(0,excelPath.LastIndexOf('\\'));
                foreach (Excel._Worksheet excelSheet in (Excel.Sheets)_workBook.Worksheets)
                {
                    if (!isEmptySheet(excelSheet))
                    {
                        string sheetName = Regex.Replace(excelSheet.Name, "[^a-zA-Z0-9]+", "");
                        if (sheetName.Length > 25)
                        {
                            sheetName = sheetName.Substring(0, 25);
                        }
                        TextTemplate page = new TextTemplate(excelSheet);
                        if (!Directory.Exists(outputLocation))
                        {
                            Directory.CreateDirectory(outputLocation);
                        }
                        else if (File.Exists(outputLocation + @"\" + sheetName + "Controls.cs"))
                        {
                            File.Delete(outputLocation + @"\" + sheetName + "Controls.cs");
                        }
                        System.IO.File.WriteAllText(outputLocation + @"\" + sheetName + "Controls.cs", page.TransformText());
                    }
                }
            }
            finally
            {
                _workBook.Close(false, _value, _value);
                _excelApplication.Quit();
            }
           
        }
        /// <summary>
        /// api through which data from the list can be write to an excel
        /// kind of a Template Method Pattern is used here
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="holdingsList"></param>
        public void WriteDateToExcel(string fileName, List<T> list, string startColumn, string endColumn, string sheetName)
        {
            try
            {
                this.ActivateExcel(fileName);
                this.FillRowData(list);
                this.FillExcelWithData();
                this.FillHeaderColumn(Headers, startColumn, endColumn);
                int i = 1;
                string temp = sheetName;
                while (_excelSheets.Contains(temp))
                {
                    temp = sheetName;
                    temp += i.ToString();
                    i++;
                }
                this._excelSheet.Name = temp;                
                this.SaveExcel(fileName);
            }
            finally
            {
                _workBook.Close(false, _value, _value);
                _excelApplication.Quit();
            }

        }        
        
        

        /// <summary>
        /// activate the excel application
        /// </summary>
        protected virtual void ActivateExcel(string fileName)
        {
            _excelApplication = new Excel.Application();
            _workBooks = (Excel.Workbooks)_excelApplication.Workbooks;
            try
            {
                _workBook = _workBooks.Open(fileName);
            }
            catch (Exception)
            {
                _workBook = (Excel._Workbook)(_workBooks.Add(_value));
            }

            if (_workBook == null)
            {
                _workBook = (Excel._Workbook)(_workBooks.Add(_value));
            }
            _excelSheets = (Excel.Sheets)_workBook.Worksheets;
            
            _excelSheet = (Excel._Worksheet)_workBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        }

        private Boolean isEmptySheet(Excel._Worksheet WorkSheet)
        {
            int row;
            int col;
            Excel.Range range = null;
            row = WorkSheet.UsedRange.Rows.Count;
            col = WorkSheet.UsedRange.Columns.Count;
            range = WorkSheet.get_Range("A1",Missing.Value);
            string value = Convert.ToString((range.Cells[1, 1] as Excel.Range).Value2); //range is Excel.Range object
            if (row == 1 && col == 1 && value == null)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// fill the header columns for the range specified and make it bold if specified
        /// </summary>
        /// <param name="headers"></param>
        /// <param name="startColumn"></param>
        /// <param name="endColumn"></param>
        protected void FillHeaderColumn(object[] headers, string startColumn, string endColumn)
        {                       
            _excelRange = _excelSheet.get_Range(startColumn, endColumn);
            _excelRange.set_Value(_value, headers);
            if (BoldHeaders == true)
            {
                this.BoldRow(startColumn, endColumn);
            }
            _excelRange.EntireColumn.AutoFit();
        }
        /// <summary>
        /// Fill the excel sheet with data along with the position specified
        /// </summary>
        /// <param name="columnrow"></param>
        /// <param name="data"></param>
        private void FillExcelWithData()
        {
            _excelRange = _excelSheet.get_Range("A1", _value);
            _excelRange = _excelRange.get_Resize(RowCount + 1, ColumnCount);
            _excelRange.set_Value(Missing.Value, ExcelData);
            _excelRange.EntireColumn.AutoFit();
        }
        /// <summary>
        /// save the excel sheet to the location with file name
        /// </summary>
        /// <param name="fileName"></param>
        protected virtual void SaveExcel(string fileName)
        {
            if (File.Exists(fileName))
            {
                _workBook.Save();
            }
            else
            {
                _workBook.SaveAs(fileName, _value, _value,
                _value, _value, _value, Excel.XlSaveAsAccessMode.xlNoChange,
                _value, _value, _value, _value, null);
            }
            
        }
        /// <summary>
        /// make the range of rows bold
        /// </summary>
        /// <param name="row1"></param>
        /// <param name="row2"></param>
        private void BoldRow(string row1, string row2)
        {            
            _excelRange = _excelSheet.get_Range(row1, row2);
            _excelFont = _excelRange.Font;
            _excelFont.Bold = true;
        }
    }

    public static class ExcelExtensions
    {
        public static bool Contains(this Excel.Sheets sheets, string sheetName)
        {
            foreach (Excel.Worksheet sheet in sheets)
            {
                if (sheet.Name.Equals(sheetName))
                    return true;
            }
            return false;
        }
    }

    public class ExcelWriter<T> : ExcelFileWriter<T>
    {
        object[,] myExcelData;

        public override object[] Headers
        {
            get
            {
                object[] headerName = { "DisplayName", "Control Type", "Properties" };
                return headerName;

            }
        }
        public override void FillRowData(List<T> list)
        {
            int listCount = list.Count;
            int count = 0;
            _list = list;

            myExcelData = new object[RowCount + 1, ColumnCount + 1];
            for (int row = 1; row <= RowCount; row++)
            {
                for (int col = 0; col < ColumnCount; col++)
                {
                    myExcelData[row, col] = list[count];
                    count++;
                }
            }

        }

        public override object[,] ExcelData
        {
            get
            {
                return myExcelData;

            }
        }
        public override int ColumnCount
        {
            get
            {
                return 3;
            }
        }
        public override int RowCount
        {
            get
            {
                return _list.Count / ColumnCount;
            }
        }
    }
  }
